package com.example.myactspringboot.server;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy;
import com.example.myactspringboot.dao.ExcelDao;
import com.example.myactspringboot.util.CommonMergeStrategy;
import org.apache.commons.lang3.ThreadUtils;
import org.apache.http.HttpResponse;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @Author Zhouchb
 * @Create 2024-01-02 19:42
 * @Description
 */
@Service
public class ExcelService {

    @Autowired
    private ExcelDao dao;

    public void export(HttpServletResponse response) {

        try {
         String   fileName = URLEncoder.encode("导出", "UTF-8");
            /*
             * 主要的集合 装表头数据
             */
            List<List<String>> list = new ArrayList<>();
            String[] arr1={"序号","序号"};
            String[] arr2={"获得双师年度","获得双师年度"};
            String[] arr3={"学院","级别"};
            String[] arr4={"学院","人工智能"};
            String[] arr5={"学院","传媒"};
            String[] arr7={"学院","合计"};
            String[] arr8={"总人数","总人数"};

            List<String> columField = new ArrayList<>();
            columField.add("rank");
            columField.add("gain_year");
            columField.add("teach_leve");
            columField.add("x");
            columField.add("z");
            columField.add("sumx");
            columField.add("allSum");
//第一列
            List<String> head0 = new ArrayList<>();
            Collections.addAll(head0, arr1);
            //第二列
            List<String> head1 = new ArrayList<>();
            Collections.addAll(head1, arr2);
            //第三列
            List<String> head2 = new ArrayList<>();
            Collections.addAll(head2, arr3);
            //第四列
            List<String> head3 = new ArrayList<>();
            Collections.addAll(head3, arr4);
            //第五列
            List<String> head4 = new ArrayList<>();
            Collections.addAll(head4, arr5);
            //第七列
            List<String> head6 = new ArrayList<>();
            Collections.addAll(head6, arr7);
            //第八列
            List<String> head7 = new ArrayList<>();
            Collections.addAll(head7, arr8);

            list.add(head0);
            list.add(head1);
            list.add(head2);
            list.add(head3);
            list.add(head4);
            list.add(head6);
            list.add(head7);

            List<CellRangeAddress> cellRangeAddressList = new ArrayList<>();

            List<Map<String, Object>> data = dao.getData();
            List<List<Object>> data1 = data(columField, data, cellRangeAddressList);

//            List<Map<String,Object>>
            // 内容合并策略
//            cellRangeAddressList.add(new CellRangeAddress(2,4,0,0));
//            cellRangeAddressList.add(new CellRangeAddress(2,4,6,6));
//            cellRangeAddressList.add(new CellRangeAddress(15,23,1,1));
//            cellRangeAddressList.add(new CellRangeAddress(24,40,1,1));
//            cellRangeAddressList.add(new CellRangeAddress(14,14,2,3));
//            cellRangeAddressList.add(new CellRangeAddress(23,23,2,3));
//            cellRangeAddressList.add(new CellRangeAddress(40,40,2,3));
//            cellRangeAddressList.add(new CellRangeAddress(41,41,1,3));

            response.setHeader("content-Type", "application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode( new String(fileName.getBytes(StandardCharsets.UTF_8), "utf-8")));
            OutputStream outputStream = response.getOutputStream();

            EasyExcel.write(outputStream).head(list).sheet("模板")
                    .registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
                    .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)35,(short)25))
                    .registerWriteHandler(new CommonMergeStrategy(cellRangeAddressList))
                    .registerWriteHandler(getCellStyle())
                    .doWrite(data1);
            outputStream.close();
        } catch (Exception e) {
            try {
                response.setHeader("Content-type", "text/html;charset=UTF-8");
                response.setCharacterEncoding("UTF-8");
                PrintWriter pw = response.getWriter();
                pw.write("导出文件异常！");
            } catch (Exception e1) {
            }
        }
    }

    private List<List<Object>> data(  List<String> columField,List<Map<String, Object>> data , List<CellRangeAddress> cellRangeAddressList) {

        List<Map<String, Object>> rowSize = dao.getRowSize();
        List<Map<String, Object>> heJi = dao.getHeJi();
        Map<String,Long> yearRowMap = new HashMap<>();
        rowSize.stream().map(item->{yearRowMap.put(item.get("gain_year").toString(), (Long) item.get("rowNum"));return item;}).collect(Collectors.toList());
        List<List<Object>> list = new ArrayList<>();
        int sign = 0;
        int merRow = 2;
        int merSignRow = 0;
        for (int i = 0; i < data.size(); i++) {
            Map<String, Object> map = data.get(i);
            List<Object> dataStr = new ArrayList<>();
            Long rowS = yearRowMap.get(map.get("gain_year"));
            if (!rowS.equals(0)){
            for (String s : columField) {
                if (s.equals("rank")){
                    Map<String, Object> rankMap = heJi.get(sign);
                    dataStr.add(rankMap.get("rank"));
                }else {
                    Object o = map.get(s);
                    dataStr.add(o);
                }
            }
                yearRowMap.put(map.get("gain_year").toString(),--rowS);
                list.add(dataStr);
                merSignRow++;
            }
            if(rowS.equals(0l)){
                List<Object> dataStr1 = new ArrayList<>();
                cellRangeAddressList.add(new CellRangeAddress(merRow,merRow+merSignRow,0,0));
                cellRangeAddressList.add(new CellRangeAddress(merRow,merRow+merSignRow,1,1));
                cellRangeAddressList.add(new CellRangeAddress(merRow,merRow+merSignRow,6,6));
                merRow=merRow+merSignRow+1;
                Map<String, Object> map1 = heJi.get(sign++);
                for (String s : columField) {
                    Object o = map1.get(s);
                    dataStr1.add(o);
                }
                list.add(dataStr1);
                merSignRow=0;
            }
        }

        return list;
    }
    private List<List<Integer>> data() {
        List<List<Integer>> list = new ArrayList<>();
        for (int i = 0; i < 9; i++) {
            List<Integer> data = new ArrayList<>();
            for (int  j= 0;j  < 8; j++) {
            data.add(j);
            }
            list.add(data);
        }
        return list;
    }

    public void mergeWithPrevCol(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex, int num) {
        Sheet sheet = writeSheetHolder.getSheet();
        CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex, curRowIndex, curColIndex, curColIndex + num);
        sheet.addMergedRegion(cellRangeAddress);
    }
    /**
     * 设置样式
     */
    private static HorizontalCellStyleStrategy getCellStyle(){
        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 背景设置
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setColor(IndexedColors.BLACK.getIndex());
        headWriteFont.setFontName("微软雅黑");
        headWriteFont.setFontHeightInPoints((short)13);
        headWriteCellStyle.setWriteFont(headWriteFont);
        headWriteCellStyle.setTopBorderColor(IndexedColors.GREEN.getIndex());
        headWriteCellStyle.setBottomBorderColor(IndexedColors.GREEN.getIndex());
        headWriteCellStyle.setLeftBorderColor(IndexedColors.GREEN.getIndex());
        headWriteCellStyle.setRightBorderColor(IndexedColors.GREEN.getIndex());
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        WriteFont contentFont = new WriteFont();
        contentFont.setFontHeightInPoints((short) 11);
        contentWriteCellStyle.setWriteFont(contentFont);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBottomBorderColor(IndexedColors.GREEN.getIndex());
        contentWriteCellStyle.setTopBorderColor(IndexedColors.GREEN.getIndex());
        contentWriteCellStyle.setLeftBorderColor(IndexedColors.GREEN.getIndex());
        contentWriteCellStyle.setRightBorderColor(IndexedColors.GREEN.getIndex());

        //设置内容靠左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        return new HorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle);
    }

    public void download(String title, HttpServletResponse response) throws IOException {
        //  excel 文件名定义
        String fileName = title + ".xlsx";
        // 下载接口响应内容定义
        response.setHeader("Charset", String.valueOf(StandardCharsets.UTF_8));
        response.setHeader("Content-Type", "application/octet-stream;charset=" + String.valueOf(StandardCharsets.UTF_8));
        response.setHeader("Content-Disposition", "attachment; filename="
                + URLEncoder.encode(fileName, String.valueOf(StandardCharsets.UTF_8)));

        // 正式写入excel数据
        // 第一步：先确定列名，也就是列标题，这个要和查出来的字段数量相一致,可以根据动态查询的字段动态生成
        String[] colums = new String[]{"姓名","年龄"};
        List<List<String>> head = new ArrayList<>(colums.length);
        for (String colum: colums) {
            List<String> list=new ArrayList(1);
            list.add(colum);
            head.add(list);
        }
        // 创建模拟数据
        List<Map> maps=new ArrayList<>();
        Map map=new HashMap();
        map.put("name","123");
        map.put("age","456");
        maps.add(map);
        // 创建接收转换好数据的容器
        List<List<Object>> sheetData=new ArrayList<>();
        // 将模拟数据转换成需要的格式
        // 解释：map类型无法直接按照key值导入，需要转换成list类型的数据，依次导入
        for (Map m : maps) {
            List list=new ArrayList(colums.length);
            for (String colum : colums) {
                // valueOf是为了防止数据类型不一致导致报错，需要特殊表格类型的自行解决
                list.add(String.valueOf(m.get(colum)));
            }
            sheetData.add(list);
        }
        // 到这里就可以直接写入数据了
        // 写入数据流，设置表头，设置自适应表头宽度
        EasyExcel.write(
                        response.getOutputStream())// 响应的数据流
                .head(head)// 设置列标题
                .needHead(true) // 表示需要生成列标题
                .sheet(title)// sheet页的名称，可省略
                //.table() // 没用到推荐不写，如果写了则会生成双标题
                //.registerWriteHandler(new SimpleColumnWidthStyleStrategy(50)) // 设置自定义列宽
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())// 设置自适应列宽
                .doWrite(sheetData);// 将数据写入
    }

    public void testAsync() throws InterruptedException {
        System.out.println("主线程");
        asyncSendEmail();
    }

    @Async("threadPoolExecutor")
    public void asyncSendEmail() throws InterruptedException {
        System.out.println("开始发送邮件:"+Thread.currentThread().getName());
        Thread.sleep(2000);
    }
}
